package com.hexinfo.dmpro.monitor.util;

import com.hexinfo.dmpro.monitor.service.serviceparam.GiveAnAlarmService;
import lombok.extern.slf4j.Slf4j;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

@Slf4j
@Component
public class ConnMysqlUtil {

    //连接配置
    @Value("${database.driver}")
    private String driver;
    @Value("${database.url}")
    private String url;
    @Value("${database.user}")
    private String user;
    @Value("${database.password}")
    private String password;
    @Value("${database.selectSql}")
    private String selectSql;
    static String deleteSql = "drop table {tableName}";

    private static final Logger logger = LoggerFactory.getLogger(GiveAnAlarmService.class);

    /**
     * 计算下个月的年月
     */
    public String getNextMonthYear() {
        Calendar cal = Calendar.getInstance();
        cal.add(Calendar.MONTH, 1);
        int nextYear = cal.get(Calendar.YEAR);
        int nextMonth = cal.get(Calendar.MONTH) + 1;
        // 格式化日期为yyyymm格式
        String formattedDate = String.format("%04d%02d", nextYear, nextMonth);
        log.info("下个月日期为：" + formattedDate);
        return formattedDate;
    }

    /**
     * 读取 resources 下的 sql文件
     * @param relPath 文本文件相对于 resources 的 相对路径
     */
    public String readSqlFile(String relPath) {
        StringBuffer sb = new StringBuffer();
        //获取项目的 classpath
        try {
            InputStream fileIs = this.getClass().getClassLoader().getResourceAsStream(relPath);
            final int maxSize = 1024;
            byte[] buff = new byte[maxSize];
            int read = 0;
            while ((read = fileIs.read(buff, 0, maxSize)) > 0) {
                sb.append(new String(buff, 0, read));
            }
            fileIs.close();
        } catch (Exception e) {
            e.printStackTrace();
            log.error("读取resources下的sql文件发生异常：e=", e);
        }
        log.info("StringBuffer sb=\r\n{}", sb);
        return sb.toString();
    }

    //连接数据库，查询表是否存在
    public Boolean selectTable(String name) throws Exception{
        String sqlData = selectSql.replace("{tableName}", name);
        String count = "2";
        Class.forName(driver);
        Connection con = DriverManager.getConnection(url, user, password);
        if (!con.isClosed()) {
            logger.info("已成功连接数据库！");
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(sqlData);
            while(rs.next()){
                count = rs.getString("count");
            }
            rs.close();
            st.close();
            con.close();
        }
        if ("1".equals(count)){
            logger.info(name+"表存在");
            return true;
        }else {
            logger.info(name+"表不存在");
            return false;
        }
    }

    //连接数据库，创建表
    public void createTable(String name, String sql) throws Exception{
        Class.forName(driver);
        Connection con = DriverManager.getConnection(url, user, password);
        if (!con.isClosed()) {
            logger.info("已成功连接数据库！");
            Statement st = con.createStatement();
            st.executeUpdate(sql);
            logger.info(name+"建表成功");
            st.close();
            con.close();
        }
    }

    //连接数据库，删除表
    public void deleteTable(String name) throws Exception{
        String sqlData = deleteSql.replace("{tableName}", name);
        Class.forName(driver);
        Connection con = DriverManager.getConnection(url, user, password);
        if (!con.isClosed()) {
            logger.info("已成功连接数据库！");
            Statement st = con.createStatement();
            st.executeUpdate(sqlData);
            logger.info(name+"删表成功");
            st.close();
            con.close();
        }
    }

    /**
     * 获取两个日期之间的所有月份 (年月)
     *
     * @param startTime
     * @param endTime
     * @return：YYYY-MM
     */
    public List<String> getMonthBetweenDate(String startTime, String endTime){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
        // 声明保存日期集合
        List<String> list = new ArrayList<String>();
        try {
            // 转化成日期类型
            Date startDate = sdf.parse(startTime);
            Date endDate = sdf.parse(endTime);
            //用Calendar 进行日期比较判断
            Calendar calendar = Calendar.getInstance();
            while (startDate.getTime()<=endDate.getTime()){
                // 把日期添加到集合
                list.add(sdf.format(startDate).replace("-",""));
                // 设置日期
                calendar.setTime(startDate);
                //把日期增加一月
                calendar.add(Calendar.MONTH, 1);
                // 获取增加后的日期
                startDate=calendar.getTime();
            }
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return list;
    }

    //获取两年前年月
    public String getYearBetweenDate(){
        SimpleDateFormat format = new SimpleDateFormat("yyyyMM");
        Calendar c = Calendar.getInstance();
        c.setTime(new Date());
        c.add(Calendar.YEAR, -2);
        Date y = c.getTime();
        String year = format.format(y);
        logger.info("过去2年的月份："+year);
        return year;
    }
}
